package dao;

import domain.EccezioneUpdater;
import domain.File;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class FileDAO {

    private static final String INSERT_SQL = "INSERT INTO file (path_file, ultima_modifica, incluso) VALUES (?,?,?)";
    private static final String SELECT_SQL = "SELECT * FROM file WHERE id_file = ?";
    private static final String UPDATE_SQL = "UPDATE file SET path_file = ?, ultima_modifica = ?, incluso = ? WHERE id_file = ?";
    private static final String DELETE_SQL = "DELETE FROM file WHERE id_file = ?";
    private static final String INSERT_LINK_IN = "INSERT INTO contained_in (root, file) VALUES (?,?)";
    private static final String DELETE_LINK_IN = "DELETE FROM contained_in WHERE file = ?";

    private FileDAO() {
    }

    public static void create(File aThis) throws EccezioneUpdater {
        Connection con = null;
        PreparedStatement st = null;
        try {
            con = ConnectionManager.getConnection();
            con.setAutoCommit(false);
            st = con.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS);
            st.setString(1, aThis.getPath_file());
            st.setTimestamp(2, new Timestamp(aThis.getUltima_modifica()));
            st.setBoolean(3, aThis.isIncluso());

            st.executeUpdate();

            ResultSet rs = st.getGeneratedKeys();
            if (rs.next()) {
                int risultato = rs.getInt(1);
                aThis.setId_file(risultato);
            }

            st = con.prepareStatement(INSERT_LINK_IN);
            st.setInt(1, aThis.getLinkIn().getRoot().getId_root());
            st.setInt(2, aThis.getLinkIn().getFile().getId_file());
            st.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
        } catch (ClassNotFoundException ex) {
            throw new EccezioneUpdater("Impossibile caricare il driver per connettersi al database");
        } catch (SQLException ex) {
            throw new EccezioneUpdater(ex.getMessage());
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
            }
        }
    }

    public static void read(File aThis) throws EccezioneUpdater {
        Connection con = null;
        PreparedStatement st = null;
        try {
            con = ConnectionManager.getConnection();
            st = con.prepareStatement(SELECT_SQL);
            st.setInt(1, aThis.getId_file());
            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                aThis.setPath_file(rs.getString("path_file"));
                aThis.setUltima_modifica(rs.getTimestamp("ultima_modifica").getTime());
                aThis.setIncluso(rs.getBoolean("incluso"));
            }
        } catch (ClassNotFoundException ex) {
            throw new EccezioneUpdater("Impossibile caricare il driver per connettersi al database");
        } catch (SQLException ex) {
            throw new EccezioneUpdater(ex.getMessage());
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
            }
        }
    }

    public static void update(File aThis) throws EccezioneUpdater {
        Connection con = null;
        PreparedStatement st = null;
        try {
            con = ConnectionManager.getConnection();
            st = con.prepareStatement(UPDATE_SQL);
            st.setString(1, aThis.getPath_file());
            st.setTimestamp(2, new Timestamp(aThis.getUltima_modifica()));
            st.setBoolean(3, aThis.isIncluso());
            st.setInt(4, aThis.getId_file());
            st.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new EccezioneUpdater("Impossibile caricare il driver per connettersi al database");
        } catch (SQLException ex) {
            throw new EccezioneUpdater(ex.getMessage());
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
            }
        }
    }

    public static void delete(File aThis) throws EccezioneUpdater {
        Connection con = null;
        PreparedStatement st = null;
        try {
            con = ConnectionManager.getConnection();
            st = con.prepareStatement(DELETE_SQL);
            st.setInt(1, aThis.getId_file());
            st.executeUpdate();
            
          //  st = con.prepareStatement(DELETE_LINK_IN);
          //  st.setInt(i, i1);
        } catch (ClassNotFoundException ex) {
            throw new EccezioneUpdater("Impossibile caricare il driver per connettersi al database");
        } catch (SQLException ex) {
            throw new EccezioneUpdater(ex.getMessage());
        } finally {
            try {
                if (con != null) {
                    con.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
            }
        }
    }
}
